Arbeitspaket (AP) 3: Management & Nutzung Räumliche Daten¶

Angaben Studierende(r) (fehlende Angaben ergänzen)¶

Vorname: Mauricio
Nachname: Saez
Immatrikulationsnummer: 12642112
Modul: Data Science
Prüfungsdatum / Raum / Zeit: 07.10.2024 / Raum: SF O3.54 / 8:00 – 11:45
Erlaubte Hilfsmittel: w.MA.XX.DS.24HS (Data Science)
Open Book, Eigener Computer, Internet-Zugang
Nicht erlaubt: Nicht erlaubt ist der Einsatz beliebiger Formen von generativer KI (z.B. Copilot, ChatGPT)
sowie beliebige Formen von Kommunikation oder Kollaboration mit anderen Menschen.

Bewertungskriterien¶

(max. erreichbare Punkte: 48)¶

Kategorie Beschreibung Punkteverteilung
Code nicht lauffähig oder Ergebnisse nicht sinnvoll Der Code enthält Fehler, die verhindern, dass er ausgeführt werden kann (z.B. Syntaxfehler) oder es werden Ergebnisse ausgegeben, welche nicht zur Fragestellung passen. 0 Punkte
Code lauffähig, aber mit gravierenden Mängeln Der Code läuft, aber die Ergebnisse sind aufgrund wesentlicher Fehler unvollständig (z.B. fehlende Joins, gravierende Fehler in SQL-Abfragen). Nur geringer Fortschritt erkennbar. 25% der max. erreichbaren Punkte
Code lauffähig, aber mit mittleren Mängeln Der Code läuft und liefert teilweise korrekte Ergebnisse, aber es gibt grössere Fehler (z.B. fehlende Spalten, unvollständige SQL-Abfragen). Die Ergebnisse sind nachvollziehbar, aber unvollständig oder ungenau. 50% der max. erreichbaren Punkte
Code lauffähig, aber mit minimalen Mängeln Der Code läuft und liefert ein weitgehend korrektes Ergebnis, aber kleinere Fehler (z.B. falsche oder fehlende Sortierung, Rundung von Werten falsch) beeinträchtigen die Vollständigkeit des Ergebnisses. 75% der max. erreichbaren Punkte
Code lauffähig und korrekt Der Code läuft einwandfrei und liefert das korrekte Ergebnis ohne Mängel. 100% der max. erreichbaren Punkte

Python Libraries und Settings¶

In [58]:
# Libraries
import os
import folium
import pandas as pd
import geopandas as gpd
from sqlalchemy import create_engine, text

# Ignore warnings
import warnings
warnings.filterwarnings("ignore")

print(os.getcwd())
/workspaces/python_postgresql_postgis

Vorbereitung (Hinweis: dieser Teil wird nicht bewertet)¶

1.) Starten Sie eine GitHub Codespaces Instanz auf Basis Ihres Forks des folgenden GitHub Repositories:¶

GitHub-Repository: https://github.com/mario-gellrich-zhaw/python_postgresql_postgis¶
WICHTIG!!! Verwenden Sie eine GitHub Codespaces Instanz mit ausreichend Arbeitsspeicher (4core, 16GB RAM).¶

Hinweis:

  • Im Unterricht wurden bereits sämtliche Installationen und Einstellungen inkl. der Registrierung des Datenbank Servers auf pgAdmin vorgenommen.
  • Falls Sie die Codespaces-Instanz neu erstellen müssen, folgen Sie bitte den detaillierten Erklärungen auf der README-Seite des GitHub Repositories.

2.) Erstellen und Testen Sie die Datenbankverbindung mit der 'osm_switzerland' Datenbank.¶

In [59]:
# Set up Database Connection
user = "pgadmin"
password = "geheim"
host = "localhost"
port = "5432"
database = "osm_switzerland"

# Erstellen der Connection URL
db_connection_url = "postgresql://" + user + ":" + password +\
                    "@" + host + ":" + port + "/" + database

# Erstellen SQLAlchemy Engine
engine = create_engine(db_connection_url)

# Test der Connection
with engine.connect() as connection:
    result = connection.execute(text('SELECT current_database()'))
    print(result.fetchone())

# Verbindung trennen
engine.dispose()
('osm_switzerland',)

Aufgaben (Dieser Teil wird bewertet!)¶

Hinweise zu den folgenden Aufgabenstellungen:

  • In diesem Jupyter Notebook gibt es jeweils zwei Code-Zellen pro Aufgabe:
    1. Eine Codezelle mit Python-Code und einem SQL-Statement für die Datenbank-Abfrage.
    2. Eine Codezelle mit Python-Code für die Kartendarstellung der Ergebnisse der jeweiligen SQL-Abfrage.
  • In den Codezellen für die Datenbank-Abfrage muss jeweils das SQL-Statement ergänzt werden.
  • In den Codezellen für die Kartendarstellung muss nur dann der Python Code ergänzt werden, wenn in der Aufgabe danach gefragt wird.
Beachten Sie, dass für die Punktevergabe auch die weiteren Anforderungen zu den Fragen unter 'Details zur Aufgabenstellung' herangezogen werden.

Aufgabe (1): Erstellen Sie eine Abfrage sämtlicher Autoreparatur-Werkstätten in der Schweiz¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in der Tabelle 'planet_osm_point'.
  • Stellen sie in der Ergebnistabelle die Spalten: osm_id, shop sowie die transformierte Geometrie als Spalte geom dar.
  • Tipp: Die Geometry wird mit Hilfe der Funktion st_transform() transformiert, z.B.: st_transform(p.way, 4326) AS geom.
  • Tipp: Autoreparatur-Werkstätten sind mit dem key:value Paar shop='car_repair' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

In [60]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            h.osm_id,
            h.shop,
            h.name,
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_point h
        WHERE h.shop = 'car_repair';"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[60]:
osm_id shop name geom
0 1811755810 car_repair Grenzgarage POINT (9.62898 47.45412)
1 9408250312 car_repair Gebr. Wirth AG POINT (9.63098 47.45327)
2 2539306181 car_repair Lantech POINT (9.58444 47.46663)
3 2530851973 car_repair Garage Welpe POINT (9.58777 47.46975)
4 3346119599 car_repair Garage Martino GmbH POINT (9.49096 47.47737)
... ... ... ... ...
1459 4386729493 car_repair Garage Stahel POINT (9.26639 47.55844)
1460 4366881989 car_repair Autoviva VW und Seat POINT (9.27967 47.54715)
1461 6092408245 car_repair Nussberger Direktimport POINT (9.34644 47.53412)
1462 1492440172 car_repair Engeler Automobile POINT (9.20147 47.63996)
1463 1493634725 car_repair Morandi Group AG POINT (9.19848 47.63967)

1464 rows × 4 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [61]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.GeoJson(
    gdf,
    name='map'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[61]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (2) Erstellen Sie eine Abfrage aller Biergärten in der Schweiz.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in den Tabellen 'planet_osm_point'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, amenity, name und die transformierte Geometrie als Spalte geom dar.
  • Tipp: Biergärten sind mit dem key:value Paar amenity='biergarten' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

In [62]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            h.osm_id,
            h.amenity,
            h.name,
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_point h
        WHERE h.amenity = 'biergarten';"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf.head()
Out[62]:
osm_id amenity name geom
0 704467869 biergarten Bierhalle POINT (9.6068 47.40694)
1 423833242 biergarten Brasserie-Bar de la Poste POINT (6.93199 46.99151)
2 746772927 biergarten Buvette de l'alpage du col du Lein POINT (7.15997 46.11045)
3 370416485 biergarten Bar des Etablons POINT (7.23621 46.14038)
4 6109596184 biergarten Dolce Vita Gelati Bar POINT (7.6897 46.68853)
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [63]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.GeoJson(
    gdf,
    name='map'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[63]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (3): Erstellen Sie eine Abfrage aller Gebäude in der Stadthausstrasse in Winterthur, welche vollständige Adressangaben besitzen.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in der Tabelle 'planet_osm_polygon'.
  • Vollständige Adressangabe bedeutet: Strassenname, Haunummer, PLZ, Gemeindename sind vorhanden.
  • Stellen Sie in der Ergebnistabelle sämtliche Adressangaben sowie die transformierte Geometrie als Spalte geom dar.
  • Verwenden Sie für die Darstellung als Hintergrundkarte ein Satellitenbild (ESRIWorldImagery) als maptile.
  • Tipp: Gebäude sind in der Spalte 'building' klassifiziert. Mit WHERE building IS NOT NULL können Sie Gebäude filtern.

(max. erreichbare Punkte: 6)

In [64]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
                p.osm_id,
                p."addr:street",
                p."addr:housenumber",
                p."addr:city",
                p."addr:postcode",
                p.building,
                st_transform(p.way, 4326) AS geom
        FROM
                public.planet_osm_polygon AS p
        WHERE
                p."addr:street" IS NOT NULL
                AND p."addr:housenumber" IS NOT NULL
                AND p."building" IS NOT NULL
                AND p."addr:city"='Winterthur'
                AND p."addr:street"='Stadthausstrasse'
                
                """

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[64]:
osm_id addr:street addr:housenumber addr:city addr:postcode building geom
0 75027485 Stadthausstrasse 31 Winterthur 8400 office POLYGON ((8.73162 47.50041, 8.73165 47.50029, ...
1 75027472 Stadthausstrasse 35 Winterthur 8400 apartments POLYGON ((8.73153 47.5004, 8.73157 47.50026, 8...
2 75027503 Stadthausstrasse 37 Winterthur 8400 apartments POLYGON ((8.73146 47.5004, 8.73149 47.50028, 8...
3 75027432 Stadthausstrasse 39 Winterthur 8400 office POLYGON ((8.73111 47.50039, 8.73115 47.50025, ...
4 230520600 Stadthausstrasse 61 Winterthur 8400 office POLYGON ((8.72952 47.5002, 8.72955 47.50006, 8...
... ... ... ... ... ... ... ...
56 24804763 Stadthausstrasse 22 Winterthur 8400 office POLYGON ((8.72448 47.49982, 8.72453 47.4997, 8...
57 26992511 Stadthausstrasse 24 Winterthur 8400 retail POLYGON ((8.72405 47.49972, 8.72407 47.49967, ...
58 134980581 Stadthausstrasse 10b Winterthur 8400 yes POLYGON ((8.72652 47.50075, 8.72661 47.50063, ...
59 9264543 Stadthausstrasse 4a Winterthur 8400 government POLYGON ((8.73111 47.50115, 8.73112 47.50085, ...
60 22301937 Stadthausstrasse 4 Winterthur 8400 apartments POLYGON ((8.73232 47.50109, 8.73233 47.50107, ...

61 rows × 7 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [65]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=17, 
               tiles='ESRIWorldImagery')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    fill_color='greenyellow'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[65]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (4): Erstellen Sie eine Abfrage aller Strassen in der Schweiz, welche als 'motorway' klassifiziert sind.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Informationen in der Tabelle 'planet_osm_roads'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, highway und die transformierte Geometrie als Spalte geom dar.
  • Tipp: Motorways sind mit dem key:value Paar highway='motorway' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 6)

In [66]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen

sql =  """-- Create buffer around major roads
        SELECT 
            1 as group_id,
            ST_TRANSFORM(p.way::geometry, 4326) AS geom
        FROM public.planet_osm_roads AS p
        WHERE
            highway = 'motorway';"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[66]:
group_id geom
0 1 LINESTRING (9.64218 47.43433, 9.6422 47.43402,...
1 1 LINESTRING (9.64232 47.43412, 9.6423 47.43469)
2 1 LINESTRING (9.6423 47.43469, 9.64232 47.43504,...
3 1 LINESTRING (9.64312 47.43793, 9.6429 47.43753,...
4 1 LINESTRING (9.64235 47.43533, 9.6424 47.43568,...
... ... ...
8263 1 LINESTRING (9.15834 47.65468, 9.15789 47.65424...
8264 1 LINESTRING (9.15371 47.65075, 9.15343 47.6503,...
8265 1 LINESTRING (9.14594 47.63688, 9.14599 47.63692...
8266 1 LINESTRING (9.14357 47.63107, 9.14364 47.63151)
8267 1 LINESTRING (9.14365 47.63224, 9.14352 47.63152)

8268 rows × 2 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [67]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=9, 
               tiles='CartoDB positron')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    line_weight=3,
    line_color='red'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[67]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (5): Erstellen Sie eine Abfrage aller Schweizer Flüsse. Generieren Sie zusätzlich Buffer um die Flüsse mit einer Breite von 2000m.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Informationen in der Tabelle 'planet_osm_line'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, waterway sowie die transformierte Geometrie als Spalte geom dar.
  • Tipp: Flüsse sind mit dem key:value Paar waterway='river' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features
  • Tipp: Per Default wird für jedes Fluss-Segment ein separater Buffer erstellt. Es ist nicht notwendig daraus einen einzelnen Buffer zu generieren.

(max. erreichbare Punkte: 8)

In [68]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """-- Create buffer around major rivers
        SELECT 
            1 as group_id,
        ST_TRANSFORM(ST_UNION(ST_Buffer(p.way::geometry, 2000)), 4326) AS geom
        FROM public.planet_osm_line AS p
        WHERE
            waterway = 'river'
            
            ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[68]:
group_id geom
0 1 MULTIPOLYGON (((5.92959 46.11614, 5.92771 46.1...
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [69]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=8, 
               tiles='CartoDB positron')

# Map settings
folium.Choropleth(
    geo_data=gdf,
    name='map',
    fill_color='greenyellow'
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[69]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (6): Erstellen Sie eine Abfrage der Bäckerei-Geschäfte in Zürich und Winterthur.¶

Details zur Aufgabenstellung:

  • Sie finden die benötigten Daten in den Tabellen 'planet_osm_point' (Backereien).
  • Verwenden Sie die Städtenamen aus den Adressangaben für die Abfrage der Bäckerei-Standorte (Zürich, Winterthur).
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, "addr:city" sowie die transformierte Geometrie als geom dar.
  • Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
  • Sortieren Sie die Bäckerei-Geschäfte aufsteigend nach osm_id.
  • Tipp: Bäckerei-Geschäfte sind mit dem key:value Paar shop='bakery' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 8)

In [70]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            h.osm_id,
            h.shop,
            h.name,
            h."addr:city",
            ST_Transform(h.way, 4326) AS geom
        FROM planet_osm_point h
        WHERE h.shop = 'bakery'
        AND h."addr:city" IN ('Winterthur','Zürich')
        ORDER BY h.osm_id ASC

        ;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf
Out[70]:
osm_id shop name addr:city geom
0 252457457 bakery Steiner Flughafebeck Zürich POINT (8.49779 47.40311)
1 266630770 bakery Brezelkönig Zürich POINT (8.48875 47.39147)
2 267879346 bakery Walter Buchmann Zürich POINT (8.51892 47.36239)
3 268602152 bakery Moser's Zürich POINT (8.54945 47.3632)
4 270794699 bakery John Baker Zürich POINT (8.56645 47.36493)
... ... ... ... ... ...
101 10082330824 bakery RAM3 Winterthur POINT (8.74193 47.49395)
102 10884168277 bakery Juliette Zürich POINT (8.5328 47.36658)
103 10946043353 bakery Babu's Bakery Zürich POINT (8.51568 47.37473)
104 10955852823 bakery Täglich Brot Zürich POINT (8.50705 47.36079)
105 11951988149 bakery Wagner Zürich POINT (8.51763 47.3698)

106 rows × 5 columns

Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [71]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=11, 
               tiles='EsriWorldImagery')

# Map settings
folium.GeoJson(
    gdf,
    name='map',
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[71]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Aufgabe (7): Erstellen Sie eine Abfrage sämtlicher Coiffeur-Geschäfte in einem Radius von 500m um den Hauptbahnhof in Zürich.¶

Details zur Aufgabenstellung:

  • Sie finden die Daten in der Tabelle 'planet_osm_point'.
  • Berechnen Sie in der Abfrage die Distanz jedes Coiffeur-Geschäfts zum Hauptbahnhof in Metern als Spalte 'distance_meters'.
  • Stellen Sie in der Ergebnistabelle die Spalten: osm_id, shop, name, distance_meters, sowie die transformierte Geometrie als geom dar.
  • Wählen Sie eine Satelliten Karte von ESRI als Hintergrundkarte (maptile).
  • Integrieren Sie in die Kartendarstellung den Namen (Spalte 'name') der Coiffeur-Geschäfte als Popup.
  • Tipp: Coiffeur-Geschäfte sind mit dem key:value Paar shop='hairdresser' in der OpenStreetMap Map-Feature Übersicht angegeben.
  • vgl: https://wiki.openstreetmap.org/wiki/Map_features

(max. erreichbare Punkte: 8)

In [72]:
# Engine für Datenbankverbindung erstellen
engine = create_engine(db_connection_url)  

# Ergänzen Sie die SQL-Abfrage, um die Aufgabe zu lösen
sql = """SELECT
            p.osm_id,
            p.shop,
            p.name,
            ST_Distance(
                ST_Transform(p.way, 4326)::geography,
                -- my coordinates
                ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography
            ) AS distance_meters,
            ST_TRANSFORM(p.way, 4326) AS geom
        FROM
            planet_osm_point AS p
        WHERE
            p.shop = 'hairdresser'
            AND ST_DWithin(
                ST_Transform(p.way, 4326)::geography,
                -- my coordinates
                ST_SetSRID(ST_MakePoint(8.53936, 47.3781), 4326)::geography,
                500
            )
        ORDER BY distance_meters;"""

# Ergebnis in GeoDataFrame abspeichern
gdf = gpd.GeoDataFrame.from_postgis(sql, engine)

# Datenbankverbindung trennen
engine.dispose()

# Zeigen des GeoDataFrames
gdf.head()
Out[72]:
osm_id shop name distance_meters geom
0 4833061523 hairdresser André Joe Coiffure 72.419054 POINT (8.54016 47.37774)
1 4424939218 hairdresser McCoiffure 111.635316 POINT (8.53791 47.37791)
2 4244059289 hairdresser Art Coiffure Kaiser 133.439602 POINT (8.53788 47.37745)
3 4424939145 hairdresser Saleh 151.462092 POINT (8.54071 47.37709)
4 693318659 hairdresser Coiffeur Insieme 168.219919 POINT (8.53992 47.37664)
Kartendarstellung Ergebnis (nur anpassen, falls in der Aufgabe danach gefragt wird)¶
In [73]:
# Projektion definieren (WGS84)
if gdf.crs is None:
    gdf.set_crs(epsg=4326, inplace=True)
else:
    pass

# Latitude und Longitude für die Zentrierung der Karte ermitteln
centroids = gdf.geometry.centroid
lon = centroids.x.mean()
lat = centroids.y.mean()

# Initialisieren der Map
m = folium.Map(location=[lat, lon], 
               zoom_start=16, 
               tiles='EsriWorldImagery')

# Map settings
folium.GeoJson(
    gdf,
    name='map',
    popup=folium.GeoJsonPopup(fields=['name'])
).add_to(m)

folium.LayerControl().add_to(m)

# Plot map
m
Out[73]:
Make this Notebook Trusted to load map: File -> Trust Notebook

Jupyter notebook --footer info-- (please always provide this at the end of each notebook)¶

In [74]:
import os
import platform
import socket
from platform import python_version
from datetime import datetime

print('-----------------------------------')
print(os.name.upper())
print(platform.system(), '|', platform.release())
print('Datetime:', datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
print('Python Version:', python_version())
print('IP Address:', socket.gethostbyname(socket.gethostname()))
print('-----------------------------------')
-----------------------------------
POSIX
Linux | 6.5.0-1025-azure
Datetime: 2024-10-07 08:55:46
Python Version: 3.12.1
IP Address: 127.0.0.1
-----------------------------------